Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


EXPLAIN PLAN Initialization

When you run SQL statements with the EXPLAIN PLAN command, the output of EXPLAIN PLAN is put into a table with the default name plan_table. You must create this table before you can run EXPLAIN PLAN. The table can be created in one of two ways:

  Using the UTLXPLAN.SQL script provided by Oracle.
  Creating the plan_table table by hand.

The plan_table table is define as follows:

SQL> describe plan_table
 Name                                      Null?    Type
     -------------------------------    --------    ----
 STATEMENT_ID                                       VARCHAR2(30)
 TIMESTAMP                                          DATE
 REMARKS                                            VARCHAR2(80)
 OPERATION                                          VARCHAR2(30)
 OPTIONS                                            VARCHAR2(30)
 OBJECT_NODE                                        VARCHAR2(128)
 OBJECT_OWNER                                       VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 OBJECT_INSTANCE                                    NUMBER(38)
 OBJECT_TYPE                                        VARCHAR2(30)
 OPTIMIZER                                          VARCHAR2(255)
 SEARCH_COLUMNS                                     NUMBER(38)
 ID                                                 NUMBER(38)
 PARENT_ID                                          NUMBER(38)
 POSITION                                           NUMBER(38)
 OTHER                                              LONG

You do not have to name the table plan_table. You can direct EXPLAIN PLAN to use a table of another name if you want.

Invoking EXPLAIN PLAN

Invoke the EXPLAIN PLAN command with the following Oracle command sequence:

EXPLAIN PLAN
    SET STATEMENT_ID = 'Testing EXPLAIN PLAN'
     INTO plan_table
    FOR
        SQL Statement;

STATEMENT_ID should reflect the statement’s function so that you can recognize it at a later time. The plan_table parameter is the name of the table you created as described in the preceding section. If the INTO clause is omitted, the command defaults to the name plan_table.

Here is an example of a completed command:

SQL> EXPLAIN PLAN
  2      SET STATEMENT_ID = 'Testing EXPLAIN PLAN'
   3      INTO plan_table
  4      FOR
  5          SELECT
  6              SUBSTR(dogname,1,20) "Dog Name",
  7               SUBSTR(breed_name,1,20) "Breed",
  8              SUBSTR(owner,1,20) "Owner"
  9          FROM
 10              dogs, breeds
 11          WHERE
 12              dogs.breed = breeds.breed
 13          ORDER BY
 14              dogs.breed;
Explained.

The results of the EXPLAIN PLAN are written into the table plan_table. The following section explains how to retrieve the information in that table.

Extracting EXPLAIN PLAN Results

The output of EXPLAIN PLAN is written to the table specified in the EXPLAIN PLAN command (by default, to the table named plan_table). You must extract this information in order to look at the results of EXPLAIN PLAN . The results can be displayed with a query such as this:

SELECT SUBSTR(LPAD(' ',2*(LEVEL-1))||operation,1,30)
||'    '||SUBSTR(options,1,15)
||'   '||SUBSTR(object_name,1,15)
||' '||SUBSTR(DECODE(id, 0, 'Cost = '||position),1,12)
"Statement Execution Plan",
SUBSTR(optimizer, 1, 10) "Optimizer"
FROM
     plan_table
START WITH
    id = 0 AND statement_id = 'Testing EXPLAIN PLAN'
CONNECT BY PRIOR
    id = parent_id
AND
    statement_id = 'Testing EXPLAIN PLAN';

This query results in the following output:

Statement Execution Plan                                          Optimizer
-------------------------------------------                       ---------
SELECT STATEMENT   Cost =                                         CHOOSE
  MERGE JOIN
    SORT JOIN
     TABLE ACCESS FULL BREEDS
    SORT JOIN
     TABLE ACCESS FULL DOGS

6 rows selected.

If the optimizer had chosen a cost-based approach, the cost of the query would have been reflected in the first line of the optimization plan. Any features such as parallel query are also reflected here.

With this information, you can tell whether your SQL statements take advantage of indexes, clusters, or hash clusters. If you use EXPLAIN PLAN, you can see precisely how your SQL statement is being executed and what effect any changes you make to the SQL statements have on the execution plan. If you change your SQL statements to take advantage of an index or a cluster, for example, you can see an immediate improvement. EXPLAIN PLAN output is ideal for pointing out your execution plan and may indicate that where you thought you were taking advantage of an index, you actually were not.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.